package com.nl.controller.web.util;


import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashSet;

import java.util.Set;



import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellReference;

public class ExcelUtil {
    public static String NO_DEFINE = "no_define";//未定义的字段
    public static String DEFAULT_DATE_PATTERN = "yyyy年MM月dd日";//默认日期格式
    public static int DEFAULT_COLOUMN_WIDTH = 17;

    /**
     * 导出Excel 97(.xls)格式 ，少量数据
     *
     * @param title       标题行
     * @param headMap     属性-列名
     * @param jsonArray   数据集
     * @param datePattern 日期格式，null则用默认日期格式
     * @param colWidth    列宽 默认 至少17个字节
     * @param out         输出流
     */
    public static void exportExcel(String title, Map<String, String> headMap, JSONArray jsonArray, String datePattern, int colWidth, OutputStream out) {
        if (datePattern == null) {
            datePattern = DEFAULT_DATE_PATTERN;
        }
        ;
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        workbook.createInformationProperties();
        workbook.getDocumentSummaryInformation().setCompany("*****公司");
        SummaryInformation si = workbook.getSummaryInformation();
        si.setAuthor("JACK");  //填加xls文件作者信息
        si.setApplicationName("导出程序"); //填加xls文件创建程序信息
        si.setLastAuthor("最后保存者信息"); //填加xls文件最后保存者信息
        si.setComments("JACK is a programmer!"); //填加xls文件作者信息
        si.setTitle("POI导出Excel"); //填加xls文件标题信息
        si.setSubject("POI导出Excel");//填加文件主题信息
        si.setCreateDateTime(new Date());
        //表头样式
        HSSFCellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFFont titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short) 20);
        titleFont.setBoldweight((short) 700);
        titleStyle.setFont(titleFont);
        // 列头样式
        HSSFCellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFFont headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerStyle.setFont(headerFont);
        // 单元格样式
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        HSSFFont cellFont = workbook.createFont();
        cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        cellStyle.setFont(cellFont);
        // 生成一个(带标题)表格
        HSSFSheet sheet = workbook.createSheet();
        // 声明一个画图的顶级管理器
        HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
        // 定义注释的大小和位置,详见文档
        HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
                0, 0, 0, (short) 4, 2, (short) 6, 5));
        // 设置注释内容
        comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
        // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
        comment.setAuthor("JACK");
        //设置列宽
        int minBytes = colWidth < DEFAULT_COLOUMN_WIDTH ? DEFAULT_COLOUMN_WIDTH : colWidth;//至少字节数
        int[] arrColWidth = new int[headMap.size()];
        // 产生表格标题行,以及设置列宽
        String[] properties = new String[headMap.size()];
        String[] headers = new String[headMap.size()];
        int ii = 0;
        for (Iterator<String> iter = headMap.keySet().iterator(); iter
                .hasNext(); ) {
            String fieldName = iter.next();

            properties[ii] = fieldName;
            headers[ii] = fieldName;

            int bytes = fieldName.getBytes().length;
            arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
            sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
            ii++;
        }
        // 遍历集合数据，产生数据行
        int rowIndex = 0;
        for (Object obj : jsonArray) {
            if (rowIndex == 50000 || rowIndex == 0) {
                if (rowIndex != 0) {
                    sheet = workbook.createSheet();
                }//如果数据超过了，则在第二页显示

                HSSFRow titleRow = sheet.createRow(0);//表头 rowIndex=0
                titleRow.createCell(0).setCellValue(title);
                titleRow.getCell(0).setCellStyle(titleStyle);
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));

                HSSFRow headerRow = sheet.createRow(1); //列头 rowIndex =1
                for (int i = 0; i < headers.length; i++) {
                    headerRow.createCell(i).setCellValue(headers[i]);
                    headerRow.getCell(i).setCellStyle(headerStyle);

                }
                rowIndex = 2;//数据内容从 rowIndex=2开始
            }
            JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
            HSSFRow dataRow = sheet.createRow(rowIndex);
            for (int i = 0; i < properties.length; i++) {
                HSSFCell newCell = dataRow.createCell(i);

                Object o = jo.get(properties[i]);
                String cellValue = "";
                if (o == null) {
                    cellValue = "";
                } else if (o instanceof Date) {
                    cellValue = new SimpleDateFormat(datePattern).format(o);
                } else {
                    cellValue = o.toString();
                }
                ;

                newCell.setCellValue(cellValue);
                newCell.setCellStyle(cellStyle);
            }
            rowIndex++;
        }
        // 自动调整宽度
        /*for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }*/
        try {
            workbook.write(out);
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出Excel 2007 OOXML (.xlsx)格式
     *
     * @param title       标题行
     * @param headMap     属性-列头
     * @param jsonArray   数据集
     * @param datePattern 日期格式，传null值则默认 年月日
     * @param colWidth    列宽 默认 至少17个字节
     * @param out         输出流
     */
    public static void exportExcelX(String title, Map<String, String> headMap, JSONArray jsonArray, String datePattern, int colWidth, OutputStream out) {
        if (datePattern == null) {
            datePattern = DEFAULT_DATE_PATTERN;
        }
        // 声明一个工作薄
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存
        workbook.setCompressTempFiles(true);
        //表头样式
        CellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short) 20);
        titleFont.setBoldweight((short) 700);
        titleStyle.setFont(titleFont);
        // 列头样式
        CellStyle headerStyle = workbook.createCellStyle();
//        headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//        headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//        headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//        headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
//        headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
//        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        Font headerFont = workbook.createFont();
        headerFont.setFontHeightInPoints((short) 12);
//        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headerStyle.setFont(headerFont);
        // 单元格样式
        CellStyle cellStyle = workbook.createCellStyle();
//        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
//        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
//        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        Font cellFont = workbook.createFont();
//        cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        cellStyle.setFont(cellFont);
        // 生成一个(带标题)表格
        SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet();
        //设置列宽
        int minBytes = colWidth < DEFAULT_COLOUMN_WIDTH ? DEFAULT_COLOUMN_WIDTH : colWidth;//至少字节数
        int[] arrColWidth = new int[headMap.size()];
        // 产生表格标题行,以及设置列宽
        String[] properties = new String[headMap.size()];
        String[] headers = new String[headMap.size()];
        int ii = 0;
        for (Iterator<String> iter = headMap.keySet().iterator(); iter
                .hasNext(); ) {
            String fieldName = iter.next();

            properties[ii] = fieldName;
            headers[ii] = headMap.get(fieldName);

            int bytes = fieldName.getBytes().length;
            arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
            sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
            ii++;
        }
        // 遍历集合数据，产生数据行
        int rowIndex = 0;
        for (Object obj : jsonArray) {
            if (rowIndex == 50000 || rowIndex == 0) {
                if (rowIndex != 0) {
                    sheet = (SXSSFSheet) workbook.createSheet();
                }//如果数据超过了，则在第二页显示

                SXSSFRow titleRow = (SXSSFRow) sheet.createRow(0);//表头 rowIndex=0
                titleRow.createCell(0).setCellValue(title);
                titleRow.getCell(0).setCellStyle(titleStyle);
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));

                SXSSFRow headerRow = (SXSSFRow) sheet.createRow(1); //列头 rowIndex =1
                for (int i = 0; i < headers.length; i++) {
                    headerRow.createCell(i).setCellValue(headers[i]);
                    headerRow.getCell(i).setCellStyle(headerStyle);

                }
                rowIndex = 2;//数据内容从 rowIndex=2开始
            }
            JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
            SXSSFRow dataRow = (SXSSFRow) sheet.createRow(rowIndex);
            for (int i = 0; i < properties.length; i++) {
                SXSSFCell newCell = (SXSSFCell) dataRow.createCell(i);
                Object o = jo.get(properties[i]);
                String cellValue = "";
                if (o == null) {
                    cellValue = "";
                } else if (o instanceof Date) {
                    cellValue = new SimpleDateFormat(datePattern).format(o);
                } else if (o instanceof Float || o instanceof Double) {
                    cellValue = new BigDecimal(o.toString()).setScale(2, BigDecimal.ROUND_HALF_UP).toString();
                } else {
                    cellValue = o.toString();
                }

                newCell.setCellValue(cellValue);
                newCell.setCellStyle(cellStyle);
            }
            rowIndex++;
        }
        // 自动调整宽度
//        for (int i = 0; i < headers.length; i++) {
//            sheet.autoSizeColumn(i);
//        }
        try {
            workbook.write(out);
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void downloadExcelFile(String title, Map<String, String> headMap, JSONArray ja, HttpServletResponse response) {
        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            ExcelUtil.exportExcelX(title, headMap, ja, null, 0, os);
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            // 设置response参数，可以打开下载页面
            response.reset();
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String((title + ".xlsx").getBytes(), "iso-8859-1"));
            response.setContentLength(content.length);
            ServletOutputStream outputStream = response.getOutputStream();
            BufferedInputStream bis = new BufferedInputStream(is);
            BufferedOutputStream bos = new BufferedOutputStream(outputStream);
            byte[] buff = new byte[8192];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
            bis.close();
            bos.close();
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }






    /**
     * 导出2
     * @param fileName
     * @param headers
     * @param dataset
     * @param isSortDataSet
     * @param response
     * @param mergeBasis
     * @param mergeCells
     * @param sumCells
     * @param timeCells
     * @throws IOException
     */

    public static void exportExelMerge(String fileName, final String[] headers, List<String[]> dataset, boolean isSortDataSet, HttpServletResponse response, final Integer[] mergeBasis, final Integer[] mergeCells, final Integer[] sumCells, final Integer[] timeCells) throws IOException {
        String title = "Sheet1";
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
        createExcelMerge(title, headers, dataset, isSortDataSet, response.getOutputStream(), mergeBasis, mergeCells, sumCells, timeCells);
        response.setStatus(HttpServletResponse.SC_OK);
        response.flushBuffer();
    }


    /**
     * @param title         文件名称
     * @param headers       表头
     * @param dataset       数据集
     * @param isSortDataSet 是否对数据排序
     * @param out           OutputStream
     * @param mergeBasis    合并基准列 可选
     * @param mergeCells    要合并的列
     * @param sumCells      要求和的列
     * @param timeCells     时间列 可选
     */
    public static void createExcelMerge(String title, final String[] headers, List<String[]> dataset, boolean isSortDataSet, OutputStream out, final Integer[] mergeBasis, final Integer[] mergeCells, final Integer[] sumCells, final Integer[] timeCells) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(title);
        sheet.setDefaultColumnWidth(15); // 设置表格默认列宽度为15个字节
        HSSFCellStyle headStyle = createHeadStyle(workbook); // 生成头部样式
        HSSFCellStyle commonDataStyle = createCommonDataStyle(workbook); // 生成一般数据样式
        HSSFCellStyle  commonDataStyleNull= createCommonDataStyleNull(workbook); // 生成一般数据样式
        HSSFCellStyle numStyle = createNumStyle(workbook); //生成数字类型保留两位小数样式
        HSSFCellStyle sumRowStyle = createSumRowStyle(workbook); //生成合计行样式

        if (headers == null || headers.length <= 0) {
            return;
        }

        HSSFRow row = sheet.createRow(0); // 产生表格标题行
        row.setHeightInPoints(30);
        for (int i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(headStyle);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        if (isSortDataSet && mergeBasis != null && mergeBasis.length > 0) { //是否排序数据
            Collections.sort(dataset, new Comparator<String[]>() {
                public int compare(String[] o1, String[] o2) {
                    String s1 = "";
                    String s2 = "";
                    for (int i = 0; i < mergeBasis.length; i++) {
                        s1 += (o1[mergeBasis[i].intValue()] + Character.valueOf((char) 127).toString());
                        s2 += (o2[mergeBasis[i].intValue()] + Character.valueOf((char) 127).toString());
                    }
                    if (timeCells != null && timeCells.length > 0) {
                        for (int i = 0; i < timeCells.length; i++) {
                            s1 += o1[timeCells[i].intValue()];
                            s2 += o2[timeCells[i].intValue()];
                        }
                    }
                    if (s1.compareTo(s2) < 0) {
                        return -1;
                    } else if (s1.compareTo(s2) == 0) {
                        return 0;
                    } else {
                        return 1;
                    }
                }
            });
        }
        // 遍历集合数据，产生数据行
        Iterator<String[]> it = dataset.iterator();
        int index = 0;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);
            String[] dataSources = it.next();
            int sum=0;
            for (int i = 0; i < dataSources.length; i++) {
                HSSFCell cell = row.createCell(i);
                cell.setCellValue(dataSources[i]);
                if(i<=1||i==headers.length-1){
                    cell.setCellStyle(headStyle);
                }else {
                    if ("无".equals(dataSources[i])){
                        cell.setCellStyle(commonDataStyleNull);
                    }else {
                        cell.setCellStyle(commonDataStyle);
                    }
                }
                if("无".equals(dataSources[i])) {
                    sum++;
                    System.out.println(sum);
                }
            }
            if(sum==dataSources.length-5){  //隐藏数据行
                row.setZeroHeight(true);
            }
        }
        try {
            if (mergeBasis != null && mergeBasis.length > 0 && mergeCells != null && mergeCells.length > 0) {
                for (int i = 0; i < mergeCells.length; i++) {
                    mergedRegion(sheet, mergeCells[i], 1, sheet.getLastRowNum(), workbook, mergeBasis);
                }
            }
            if (sumCells != null && sumCells.length > 0) {
                createSumRow(sheet, row, headers, sumCells, sumRowStyle, numStyle);
            }
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 创建合计行
     *
     * @param sheet
     * @param row
     * @param headers
     * @param sumCells
     * @param sumRowStyle
     * @param numStyle
     */
    private static void createSumRow(HSSFSheet sheet, HSSFRow row, final String[] headers, final Integer[] sumCells, HSSFCellStyle sumRowStyle, HSSFCellStyle numStyle) {
        row = sheet.createRow(sheet.getLastRowNum() + 1);
        for (int i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(sumRowStyle);
        }
        for (int i = 1; i < sheet.getLastRowNum(); i++) {
            for (int j = 0; j < sumCells.length; j++) {
                sheet.getRow(i).getCell(sumCells[j]).setCellValue(Double.parseDouble(sheet.getRow(i).getCell(sumCells[j]).getStringCellValue()));
                sheet.getRow(i).getCell(sumCells[j]).setCellStyle(numStyle);
            }
        }
        HSSFCell sumCell = row.getCell(0);
        sumCell.setCellValue("合计：");
        String sumFunctionStr = null;
        for (int i = 0; i < sumCells.length; i++) {
            sumFunctionStr = "SUM(" + CellReference.convertNumToColString(sumCells[i]) + "2:" + CellReference.convertNumToColString(sumCells[i]) + sheet.getLastRowNum() + ")";
            row.getCell(sumCells[i]).setCellFormula(sumFunctionStr);
        }
    }

    /**
     * 合并单元格
     *
     * @param sheet
     * @param cellLine
     * @param startRow
     * @param endRow
     * @param workbook
     * @param mergeBasis
     */
    private static void mergedRegion(HSSFSheet sheet, int cellLine, int startRow, int endRow, HSSFWorkbook workbook, Integer[] mergeBasis) {
        HSSFCellStyle style = workbook.createCellStyle();           // 样式对象
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  // 垂直
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);             // 水平
        String s_will = sheet.getRow(startRow).getCell(cellLine).getStringCellValue();  // 获取第一行的数据,以便后面进行比较
        int count = 0;
        Set<Integer> set = new HashSet<Integer>();
        for (int i = 2; i <= endRow; i++) {
            String s_current = sheet.getRow(i).getCell(cellLine).getStringCellValue();
            if (s_will.equals(s_current)) {
                boolean isMerge = true;
                if (!set.contains(cellLine)) {//如果不是作为基准列的列 需要所有基准列都相同
                    for (int j = 0; j < mergeBasis.length; j++) {
                        if (!sheet.getRow(i).getCell(mergeBasis[j]).getStringCellValue()
                                .equals(sheet.getRow(i - 1).getCell(mergeBasis[j]).getStringCellValue())) {
                            isMerge = false;
                        }
                    }
                } else {//如果作为基准列的列 只需要比较列号比本列号小的列相同
                    for (int j = 0; j < mergeBasis.length && mergeBasis[j] < cellLine; j++) {
                        if (!sheet.getRow(i).getCell(mergeBasis[j]).getStringCellValue()
                                .equals(sheet.getRow(i - 1).getCell(mergeBasis[j]).getStringCellValue())) {
                            isMerge = false;
                        }
                    }
                }
                if (isMerge) {
                    count++;
                } else {
                    sheet.addMergedRegion(new CellRangeAddress(startRow, startRow + count, cellLine, cellLine));
                    startRow = i;
                    s_will = s_current;
                    count = 0;
                }
            } else {
                sheet.addMergedRegion(new CellRangeAddress(startRow, startRow + count, cellLine, cellLine));
                startRow = i;
                s_will = s_current;
                count = 0;
            }
            if (i == endRow && count > 0) {
                sheet.addMergedRegion(new CellRangeAddress(startRow, startRow + count, cellLine, cellLine));
            }
        }
    }

    /**
     * 标题单元格样式
     *
     * @param workbook
     * @return
     */
    private static HSSFCellStyle createHeadStyle(HSSFWorkbook workbook) {
        //标题单元格样式
        HSSFCellStyle headStyle = workbook.createCellStyle();
        headStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
        headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        headStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //标题单元格字体
        HSSFFont headFont = workbook.createFont();
        headFont.setColor(HSSFColor.VIOLET.index);
        headFont.setFontHeightInPoints((short) 11);
        headFont.setFontName("宋体");
        // 把字体应用到当前的样式
        headStyle.setFont(headFont);
        return headStyle;
    }

    /**
     * 合计行单元格样式
     *
     * @param workbook
     * @return
     */
    private static HSSFCellStyle createSumRowStyle(HSSFWorkbook workbook) {
        //合计行单元格样式
        HSSFCellStyle sumRowStyle = workbook.createCellStyle();
        sumRowStyle.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        sumRowStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        sumRowStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        sumRowStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        sumRowStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        sumRowStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        sumRowStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        sumRowStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
        //合计行单元格字体
        HSSFFont sumRowFont = workbook.createFont();
        sumRowFont.setColor(HSSFColor.VIOLET.index);
        sumRowFont.setFontHeightInPoints((short) 12);
        sumRowFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 把字体应用到当前的样式
        sumRowStyle.setFont(sumRowFont);
        return sumRowStyle;
    }

    /**
     * 普通数据单元格样式
     *
     * @param workbook
     * @return
     */
    private static HSSFCellStyle createCommonDataStyle(HSSFWorkbook workbook) {
        //普通数据单元格样式
        HSSFCellStyle commonDataStyle = workbook.createCellStyle();
        commonDataStyle.setFillForegroundColor(HSSFColor.WHITE.index);
        commonDataStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        commonDataStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        commonDataStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        commonDataStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        commonDataStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        commonDataStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        commonDataStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //普通数据单元格字体
        HSSFFont commonDataFont = workbook.createFont();
        commonDataFont.setFontHeightInPoints((short) 11);
        commonDataFont.setFontName("宋体");
        commonDataFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        //把字体应用到当前的样式
        commonDataStyle.setFont(commonDataFont);
        return commonDataStyle;
    }
    /**
     * 普通数据单元格样式没有数据无
     *
     * @param workbook
     * @return
     */
    private static HSSFCellStyle createCommonDataStyleNull(HSSFWorkbook workbook) {
        //普通数据单元格样式
        HSSFCellStyle commonDataStyle = workbook.createCellStyle();
        commonDataStyle.setFillForegroundColor(HSSFColor.RED.index);
        commonDataStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        commonDataStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        commonDataStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        commonDataStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        commonDataStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        commonDataStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        commonDataStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        //普通数据单元格字体
        HSSFFont commonDataFont = workbook.createFont();
        commonDataFont.setFontHeightInPoints((short) 11);
        commonDataFont.setFontName("宋体");
        commonDataFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        //把字体应用到当前的样式
        commonDataStyle.setFont(commonDataFont);
        return commonDataStyle;
    }
    /**
     * 自定义保留两位小数数字单元格格式
     *
     * @param workbook
     * @return
     */
    private static HSSFCellStyle createNumStyle(HSSFWorkbook workbook) {
        //自定义保留两位小数数字单元格格式
        HSSFCellStyle numStyle = workbook.createCellStyle();
        numStyle.setFillForegroundColor(HSSFColor.WHITE.index);
        numStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        numStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        numStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        numStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        numStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        numStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        numStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        numStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));
        //自定义保留两位小数数字单元格字体
        HSSFFont numFont = workbook.createFont();
        numFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        //把字体应用到当前的样式
        numStyle.setFont(numFont);
        return numStyle;
    }

    public static void main(String[] args) {
        String[] headers = {"大区", "部门", "金额", "数量", "日期"};
        List<String[]> dataset = new ArrayList<String[]>();
        dataset.add(new String[]{"华东", "部门3", "35", "1", "2015-01-01"});
        dataset.add(new String[]{"华北", "部门1", "20", "1", "2015-01-02"});
        dataset.add(new String[]{"华北", "部门2", "25", "1", "2015-01-03"});
        dataset.add(new String[]{"华北", "部门5", "25", "1", "2015-01-04"});
        dataset.add(new String[]{"华南", "部门1", "15", "1", "2015-01-05"});
        dataset.add(new String[]{"华北", "部门3", "30", "1", "2015-01-06"});
        dataset.add(new String[]{"华北", "部门3", "30", "1", "2015-01-07"});
        dataset.add(new String[]{"华东", "部门1", "25", "1", "2015-01-08"});
        dataset.add(new String[]{"华南", "部门4", "30", "1", "2015-01-09"});
        dataset.add(new String[]{"华东", "部门2", "25", "1", "2015-01-10"});
        dataset.add(new String[]{"华东", "部门2", "25", "1", "2015-01-11"});
        dataset.add(new String[]{"华东", "部门3", "35", "1", "2015-01-12"});
        dataset.add(new String[]{"华南", "部门1", "15", "1", "2015-01-13"});
        dataset.add(new String[]{"华北", "部门6", "20", "1", "2015-01-14"});
        dataset.add(new String[]{"华南", "部门2", "25", "1", "2015-01-15"});
        dataset.add(new String[]{"华南", "部门2", "25", "1", "2015-01-16"});
        dataset.add(new String[]{"华东", "部门1", "25", "1", "2015-01-17"});
        dataset.add(new String[]{"华南", "部门8", "30", "1", "2015-01-18"});
        dataset.add(new String[]{"华东", "部门2", "35", "1", "2015-01-01"});
        dataset.add(new String[]{"华北", "部门1", "20", "1", "2015-01-02"});
        dataset.add(new String[]{"华北", "部门11", "25", "1", "2015-01-03"});
        dataset.add(new String[]{"华北", "部门2", "25", "1", "2015-01-04"});
        dataset.add(new String[]{"华南", "部门1", "15", "1", "2015-01-05"});
        dataset.add(new String[]{"华北", "部门4", "30", "1", "2015-01-06"});
        dataset.add(new String[]{"华北", "部门3", "30", "1", "2015-01-07"});
        dataset.add(new String[]{"华东", "部门9", "25", "1", "2015-01-08"});
        dataset.add(new String[]{"华南", "部门3", "30", "1", "2015-01-09"});
        dataset.add(new String[]{"华东", "部门12", "25", "1", "2015-01-10"});
        dataset.add(new String[]{"华东", "部门2", "25", "1", "2015-01-11"});
        dataset.add(new String[]{"华东", "部门12", "35", "1", "2015-01-12"});
        dataset.add(new String[]{"华南", "部门1", "15", "1", "2015-01-13"});
        dataset.add(new String[]{"华北", "部门11", "20", "1", "2015-01-14"});
        dataset.add(new String[]{"华南", "部门21", "25", "1", "2015-01-15"});
        dataset.add(new String[]{"华南", "部门2", "25", "1", "2015-01-16"});
        dataset.add(new String[]{"华东", "部门15", "25", "1", "2015-01-17"});
        dataset.add(new String[]{"华南", "部门3", "30", "1", "2015-01-18"});
        dataset.add(new String[]{"华东", "部门3", "35", "1", "2015-01-01"});
        dataset.add(new String[]{"华北", "部门17", "20", "1", "2015-01-02"});
        dataset.add(new String[]{"华北", "部门22", "25", "1", "2015-01-03"});
        dataset.add(new String[]{"华北", "部门2", "25", "1", "2015-01-04"});
        dataset.add(new String[]{"华南", "部门1", "15", "1", "2015-01-05"});
        dataset.add(new String[]{"华北", "部门2", "30", "1", "2015-01-06"});
        dataset.add(new String[]{"华北", "部门3", "30", "1", "2015-01-07"});
        dataset.add(new String[]{"华东", "部门1", "25", "1", "2015-01-08"});
        dataset.add(new String[]{"华南", "部门8", "30", "1", "2015-01-09"});
        dataset.add(new String[]{"华东", "部门2", "25", "1", "2015-01-10"});
        dataset.add(new String[]{"华东", "部门2", "25", "1", "2015-01-11"});
        dataset.add(new String[]{"华东", "部门3", "35", "1", "2015-01-12"});
        dataset.add(new String[]{"华南", "部门7", "15", "1", "2015-01-13"});
        dataset.add(new String[]{"华北", "部门1", "20", "1", "2015-01-14"});
        dataset.add(new String[]{"华南", "部门2", "25", "1", "2015-01-15"});
        dataset.add(new String[]{"华南", "部门2", "25", "1", "2015-01-16"});
        dataset.add(new String[]{"华东", "部门9", "25", "1", "2015-01-17"});
        dataset.add(new String[]{"华南", "部门3", "30", "1", "2015-01-18"});
        try {
            OutputStream out = new FileOutputStream("E://a.xls");
            ExcelUtil.createExcelMerge("测试.xls", headers, dataset, true, out, new Integer[]{0, 1}, new Integer[]{0, 1}, new Integer[]{2, 3}, new Integer[]{4});
            out.close();
            System.out.println("excel导出成功！");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}
